package database;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.Balise;
import model.Document;
import model.Emplacement;
import model.Mot;
import ponderation.TFIDFCalculator;

import com.google.common.base.Joiner;

/**
 * Classe regroupant toutes les requêtes réalisées sur la base de données
 */
public class SQLHelper
{
  private SQLHelper()
  {
  }

  /**
   * Execute la requête passée en paramètre
   * 
   * @param query
   *          la requête à executer
   */
  public static void executeUpdate(String query)
  {
    Connection conn = DatabaseManager.INSTANCE.getDatabaseConnection();
    Statement statement;
    try
    {
      statement = conn.createStatement();
      statement.executeUpdate(query);
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }

  public static ResultSet executeQuery(String query)
  {
    Connection conn = DatabaseManager.INSTANCE.getDatabaseConnection();
    Statement statement;
    ResultSet result = null;
    try
    {
      statement = conn.createStatement();
      result = statement.executeQuery(query);
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    return result;
  }

  /**
   * Vide toutes les tables de la base
   */
  public static void emptyDatabase()
  {
    Connection conn = DatabaseManager.INSTANCE.getDatabaseConnection();
    Statement statement;
    try
    {
      statement = conn.createStatement();
      statement.executeUpdate("DELETE FROM Emplacement");
      statement.executeUpdate("DELETE FROM Balise");
      statement.executeUpdate("DELETE FROM Terme");
      statement.executeUpdate("DELETE FROM Document");
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }

  /**
   * Insère la liste des fichiers à indexer dans la table document
   * 
   * @param files
   *          liste de {@link File} à insérer
   */
  public static void insertFilesInDatabase(List<File> files)
  {
    /**
     * Insertion de document V0.1 : - On suppose que tous les documents sont au
     * même endroit (on ne stock pas le path) - On ne teste pas si un document
     * existe déjà ou que son contenu a changé (utilisation de Sha1Encryption)
     */

    Connection conn = DatabaseManager.INSTANCE.getDatabaseConnection();
    Statement statement;
    try
    {
      statement = conn.createStatement();
      for (File file : files)
      {
        String name = file.getName();
        String insertQuery = "INSERT INTO Document (nom) values ('" + name + "')";
        statement.executeUpdate(insertQuery);
      }
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }

  public static List<Document> insertDocumentsContent(List<Document> lemmatisedDocuments)
  {
    long start1 = System.currentTimeMillis();
    List<Document> documentsInserted = insertBalisesAndTermes(lemmatisedDocuments);
    long stop1 = System.currentTimeMillis();
    
    System.out.println("5.1 - Fill database (balise & terms) : " + (stop1 - start1) + "ms");

    long start2 = System.currentTimeMillis();
    List<Document> documentsWithBalisesLinked = linkBalisesWithId(documentsInserted);
    long stop2 = System.currentTimeMillis();
    
    System.out.println("5.2 - Link balises with ID : " + (stop2 - start2) + "ms");

    long start3 = System.currentTimeMillis();
    List<Document> documentsWithTermLinked = linkTermesWithId(documentsWithBalisesLinked);
    long stop3 = System.currentTimeMillis();
    
    System.out.println("5.3 - Link terms with ID : " + (stop3 - start3) + "ms");

    long start4 = System.currentTimeMillis();
    List<Document> documentsWithEmplacements = insertEmplacements(documentsWithTermLinked);
    long stop4 = System.currentTimeMillis();
    
    System.out.println("5.4 - Fill database (emplacements) : " + (stop4 - start4) + "ms");
    
    return documentsWithEmplacements;
  }

  /**
   * Insert balises and termes.
   * 
   * @param documents
   *          the lemmatised documents
   * @return
   */
  private static List<Document> insertBalisesAndTermes(List<Document> documents)
  {
    String baliseQuery = "INSERT INTO Balise (nom, chemin, Document_idDoc, baliseNumber) values ";
    String termeQuery = "INSERT INTO Terme (valeur) values ";
    List<String> alreadyInsertedTerms = new ArrayList<>();
    List<String> baliseValues = new ArrayList<>();
    List<String> termeValues = new ArrayList<>();

    List<Document> newDocuments = new ArrayList<>();
    for (Document document : documents)
    {
      List<Balise> newBalises = new ArrayList<>();
      // Insertion des balises dont la liste de mot n'est pas vide
      for (Balise balise : document.getBalises())
      {
        // Insertion des termes
        List<Mot> mots = balise.getMots();
          for (Mot terme : mots)
          {
            if (!alreadyInsertedTerms.contains(terme.getValue()))
            {
              alreadyInsertedTerms.add(terme.getValue());
              termeValues.add("('" + terme.getValue() + "')");
            }
          }
          baliseValues.add("('" + balise.getNom() + "','" + balise.getChemin() + "','" + balise.getDocument().getId()
              + "','" + balise.getBaliseNumber() + "')");
          newBalises.add(balise);
      }
      newDocuments.add(new Document(document.getId(), document.getName(), newBalises));
    }

    // Database performance : insert all the terms of a document at once
    SQLHelper.executeUpdate(termeQuery + Joiner.on(",").join(termeValues));

    // Database performance : insert all the balises of all documents at one
    SQLHelper.executeUpdate(baliseQuery + Joiner.on(",").join(baliseValues));

    return newDocuments;
  }

  /**
   * Insert emplacement in database
   * 
   * @param documentsWithTermLinked
   *          the documents with term linked
   */
  private static List<Document> insertEmplacements(List<Document> documentsWithTermLinked)
  {
    String emplacementQuery = "INSERT INTO Emplacement (Balise_idBalise, Terme_idTerme, poids) values ";
    List<String> emplacementValues = new ArrayList<>();
    List<Document> newListDocument = new ArrayList<>();

    TFIDFCalculator calculator = new TFIDFCalculator(documentsWithTermLinked);
    
    for (Document document : documentsWithTermLinked)
    {
      List<Emplacement> emplacements = new ArrayList<>();
      for (Balise balise : document.getBalises())
      {
        String uniqueID = balise.getUniqueID();
        for (Mot terme : calculator.getTermsForBalise(uniqueID))
        {
          double tf = calculator.getTF(terme, uniqueID);
          double idf = calculator.getIDF(terme);
          double weight = tf * idf;
          emplacementValues.add("(" + balise.getId() + "," + terme.getId() + "," + weight + ")");
          emplacements.add(new Emplacement(weight, 0, balise, terme));
        }
      }
      newListDocument.add(new Document(document, emplacements));
    }

    SQLHelper.executeUpdate(emplacementQuery + Joiner.on(",").join(emplacementValues));
    
    return newListDocument;
  }

  /**
   * Lie les balises du parsing avec les balises insérées dans la base pour
   * avoir leur ID
   * 
   * @param documents
   *          documents contenant les balises sans ID
   */
  private static List<Document> linkBalisesWithId(List<Document> documents)
  {
    ResultSet rs = SQLHelper.executeQuery("SELECT idBalise, nom, chemin, Document_idDoc, baliseNumber FROM Balise");
    Map<String, Balise> balisesWithId = new HashMap<>();
    try
    {
      while (rs.next())
      {
        String uniqueId = rs.getString("chemin") + rs.getString("nom") + rs.getInt("baliseNumber") + rs.getInt("Document_idDoc");
        balisesWithId.put(uniqueId, new Balise(rs.getInt("idBalise"), null, null, null, null, -1));
      }
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }

    List<Document> result = new ArrayList<>();
    for (Document document : documents)
    {
      List<Balise> newBalises = new ArrayList<>();
      for (Balise balise : document.getBalises())
      {
        Balise baliseWithId = balisesWithId.get(balise.getUniqueID());
        if (baliseWithId != null)
        {
          newBalises.add(balise.merge(baliseWithId));
        }
      }
      result.add(new Document(document.getId(), document.getName(), newBalises));
    }
    return result;
  }

  /**
   * Lie les termes du parsing avec les termes insérées dans la base pour avoir
   * leur ID
   * 
   * @param documents
   *          documents contenant les balises sans ID
   */
  private static List<Document> linkTermesWithId(List<Document> documents)
  {
    ResultSet rs = SQLHelper.executeQuery("SELECT idTerme, valeur FROM Terme");
    Map<String, Mot> termesWithID = new HashMap<>();
    try
    {
      while (rs.next())
      {
        String valeur = rs.getString("valeur");
        termesWithID.put(valeur, new Mot(rs.getInt("idTerme"), null));
      }
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }

    List<Document> result = new ArrayList<>();
    for (Document document : documents)
    {
      List<Balise> newBalises = new ArrayList<>();
      for (Balise balise : document.getBalises())
      {
        List<Mot> newMots = new ArrayList<>();
        for (Mot termeWithoutID : balise.getMots())
        {
          Mot motWithId = termesWithID.get(termeWithoutID.getValue());
          newMots.add(termeWithoutID.merge(motWithId));
        }
        newBalises.add(new Balise(balise.getId(), balise.getNom(), balise.getChemin(), balise.getDocument(), newMots,
            balise.getBaliseNumber()));
      }
      result.add(new Document(document.getId(), document.getName(), newBalises));
    }

    return result;
  }

  /**
   * Récupère tous les documents qui sont présents dans la base de données
   * 
   * @return liste de {@link Document}
   */
  public static List<Document> getDocumentsInDatabase()
  {
    List<Document> listDocs = new ArrayList<>();

    Connection conn = DatabaseManager.INSTANCE.getDatabaseConnection();
    try
    {
      Statement st = conn.createStatement();
      ResultSet rset = null;

      String req = "SELECT idDoc, nom FROM Document";
      rset = st.executeQuery(req);

      while (rset.next())
      {
        listDocs.add(new Document(rset.getInt("idDoc"), rset.getString("nom"), new ArrayList<Balise>()));
      }
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }

    return listDocs;
  }
  
  /**
   * Gets the documents in database in a map that link each name of a {@link Document}
   * with it's corresponding object.
   *
   * @return the documents in database as map
   */
  public static Map<String, Document> getDocumentsInDatabaseAsMap()
  {
    List<Document> documentsInDatabase = getDocumentsInDatabase();
    Map<String, Document> documentsAsMap = new HashMap<>();
    for (Document document : documentsInDatabase)
    {
      documentsAsMap.put(document.getName(), document);
    }
    return documentsAsMap;
  }
}
